﻿Imports System.Data.SqlClient
Public Class frmRCumpleaños
    Dim mdsDatos As New DataSet
    Dim NumMes As Integer
    Private Sub frmRCumpleaños_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        ToolStrip1.Cursor = Cursors.Hand
        ToolStrip1.RenderMode = System.Windows.Forms.ToolStripRenderMode.System

        Me.lstMeses.Text = "(Seleccionar un mes)"
        radioTodos.Checked = True

        CargaDeptos()

    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)


    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)


    End Sub
    Sub CargaDeptos()
        Dim cnconn As New SqlConnection
        cnconn.ConnectionString = CitraConnection

        Dim cmdBodega As New SqlCommand

        cmdBodega.Connection = cnconn
        cmdBodega.CommandText = "Select IdDepartamento, Descripcion " & _
                                    " From Departamentos " & _
                                    " Order By Descripcion "
        Dim daDesarrollo As New SqlDataAdapter
        daDesarrollo.SelectCommand = cmdBodega

        If mdsDatos.Tables.Contains("Departamentos") Then
            mdsDatos.Tables("Departamentos").Rows.Clear()
        End If

        daDesarrollo.Fill(mdsDatos, "Departamentos")

        lstDepartamento.DataSource = mdsDatos.Tables("Departamentos").DefaultView
        lstDepartamento.DisplayMember = "Descripcion"
        lstDepartamento.ValueMember = "IdDepartamento"


    End Sub
    Sub todos()
        Dim xl As Object
        Dim wb As Object
        xl = CreateObject("Excel.Application")
        xl.Visible = True
        xl.Workbooks.Add()
        wb = xl.activeworkbook
        Dim rng As Integer
        Dim rngORGN As Integer

        rng = 6

        wb.SHEETS(1).cells(1, 1).value = "Imprimiendo..."
        wb.SHEETS(1).columns(1).columnwidth = 15

        Dim cnConn As New SqlConnection
        cnConn.ConnectionString = CitraConnection

        Dim strSql As String = ""
        BuscaMes()

        strSql = "select IDEmpleado,D.Descripcion as depto,NombreCompleto,day(FechaNacimiento) as dia,FechaNacimiento"
        strSql = strSql & " from Empleados E INNER JOIN Departamentos D"
        strSql = strSql & " ON E.IdDepartamento=D.IdDepartamento and EstadoEmpleado<>'B'"
        strSql = strSql & " order by dia"


        Dim cmdBuscar As New SqlCommand
        cmdBuscar.Connection = cnConn
        cmdBuscar.CommandText = strSql
        Dim rdBuscar As SqlDataReader
        cnConn.Open()
        rdBuscar = cmdBuscar.ExecuteReader

        wb.SHEETS(1).cells(rng, 1).value = "Nombre"
        wb.SHEETS(1).cells(rng, 2).value = "Departamento"
        wb.SHEETS(1).cells(rng, 3).value = "Cumpleaños"
        Dim i As Integer
        For i = 1 To 3
            wb.SHEETS(1).cells(rng, i).font.bold = True
            wb.SHEETS(1).cells(rng, i).font.size = 9
        Next

        Dim fNac As Date

        rng = rng + 1
        rngORGN = rng
        Dim cont As Integer = 0
        Do While rdBuscar.Read()
            fNac = rdBuscar("FechaNacimiento")
            If fNac.Month = NumMes Then
                cont = cont + 1

                wb.SHEETS(1).cells(rng, 1).value = rdBuscar("Nombrecompleto").ToString.Trim
                wb.SHEETS(1).cells(rng, 2).value = rdBuscar("DEPTO").ToString.Trim
                wb.SHEETS(1).cells(rng, 3).value = fNac.Day & "/" & fNac.Month & "/" & Now.Year
                wb.SHEETS(1).cells(rng, 4).value = "  ( " & fNac.Day & " de " & Me.lstMeses.Text & ")"
                For i = 1 To 4
                    wb.SHEETS(1).cells(rng, i).font.size = 9
                Next
                rng = rng + 1
            End If
        Loop

        wb.SHEETS(1).COLUMNS(1).ColumnWidth = 35
        wb.SHEETS(1).COLUMNS(2).ColumnWidth = 35
        wb.SHEETS(1).COLUMNS(3).ColumnWidth = 10


        wb.SHEETS(1).cells(1, 1).value = EmpresaActiva
        wb.SHEETS(1).cells(1, 1).FONT.SIZE = 14
        wb.SHEETS(1).cells(2, 1).value = "Informe de Cumpleaños del mes de " & Me.lstMeses.Text
        wb.SHEETS(1).cells(2, 1).FONT.SIZE = 10
        wb.SHEETS(1).cells(3, 1).value = "Impreso por: " & UsuarioActivo & " el " & Now().Day & "-" & Now().Month & "-" & Now().Year
        wb.SHEETS(1).cells(3, 1).FONT.SIZE = 8
        wb.SHEETS(1).cells(4, 1).value = "Registros del filtro: " & cont
        wb.SHEETS(1).cells(4, 1).FONT.SIZE = 8
    End Sub
    Sub BuscaMes()
        Dim mes As String = Me.lstMeses.Text.Trim

        Select Case mes
            Case "Enero"
                NumMes = 1
            Case "Febrero"
                NumMes = 2
            Case "Marzo"
                NumMes = 3
            Case "Abril"
                NumMes = 4
            Case "Mayo"
                NumMes = 5
            Case "Junio"
                NumMes = 6
            Case "Julio"
                NumMes = 7
            Case "Agosto"
                NumMes = 8
            Case "Septiembre"
                NumMes = 9
            Case "Octubre"
                NumMes = 10
            Case "Noviembre"
                NumMes = 11
            Case "Diciembre"
                NumMes = 12
        End Select
    End Sub
    Sub Depto()
        Dim xl As Object
        Dim wb As Object
        xl = CreateObject("Excel.Application")
        xl.Visible = True
        xl.Workbooks.Add()
        wb = xl.activeworkbook
        Dim rng As Integer
        rng = 6

        wb.SHEETS(1).cells(1, 1).value = "Imprimiendo..."
        wb.SHEETS(1).columns(1).columnwidth = 15

        Dim cnConn As New SqlConnection
        cnConn.ConnectionString = CitraConnection

        Dim strSql As String = ""
        BuscaMes()

        strSql = "select IDEmpleado,D.Descripcion as depto,NombreCompleto,day(FechaNacimiento) as dia,FechaNacimiento"
        strSql = strSql & " from Empleados E INNER JOIN Departamentos D"
        strSql = strSql & " ON E.IdDepartamento=D.IdDepartamento and EstadoEmpleado<>'B' and D.Descripcion='" & Me.lstDepartamento.Text & "'"
        strSql = strSql & " order by dia"


        Dim cmdBuscar As New SqlCommand
        cmdBuscar.Connection = cnConn
        cmdBuscar.CommandText = strSql
        Dim rdBuscar As SqlDataReader
        cnConn.Open()
        rdBuscar = cmdBuscar.ExecuteReader

        wb.SHEETS(1).cells(rng, 1).value = "Nombre"
        wb.SHEETS(1).cells(rng, 2).value = "Departamento"
        wb.SHEETS(1).cells(rng, 3).value = "Cumpleaños"
        Dim i As Integer
        For i = 1 To 3
            wb.SHEETS(1).cells(rng, i).font.bold = True
            wb.SHEETS(1).cells(rng, i).font.size = 9
        Next

        Dim fNac As Date

        rng = rng + 1
        Dim cont As Integer = 0
        Do While rdBuscar.Read()
            fNac = rdBuscar("FechaNacimiento")
            If fNac.Month = NumMes Then
                cont = cont + 1
                wb.SHEETS(1).cells(rng, 1).value = rdBuscar("NombreCompleto").ToString.Trim
                wb.SHEETS(1).cells(rng, 2).value = rdBuscar("Depto").ToString.Trim
                wb.SHEETS(1).cells(rng, 3).value = fNac.Day & "/" & fNac.Month & "/" & Now.Year
                wb.SHEETS(1).cells(rng, 4).value = "  ( " & fNac.Day & " de " & Me.lstMeses.Text & ")"
                For i = 1 To 4
                    wb.SHEETS(1).cells(rng, i).font.size = 9
                Next
                rng = rng + 1
            End If
        Loop

        wb.SHEETS(1).COLUMNS(1).ColumnWidth = 35
        wb.SHEETS(1).COLUMNS(2).ColumnWidth = 35
        wb.SHEETS(1).COLUMNS(3).ColumnWidth = 10


        wb.SHEETS(1).cells(1, 1).value = EmpresaActiva
        wb.SHEETS(1).cells(1, 1).FONT.SIZE = 14
        wb.SHEETS(1).cells(2, 1).value = "Informe de Cumpleaños del mes de " & Me.lstMeses.Text & ". Departamento: " & Me.lstDepartamento.Text
        wb.SHEETS(1).cells(2, 1).FONT.SIZE = 10
        wb.SHEETS(1).cells(3, 1).value = "Impreso por: " & UsuarioActivo & " el " & Now().Day & "-" & Now().Month & "-" & Now().Year
        wb.SHEETS(1).cells(3, 1).FONT.SIZE = 8
        wb.SHEETS(1).cells(4, 1).value = "Registros del filtro: " & cont
        wb.SHEETS(1).cells(4, 1).FONT.SIZE = 8
    End Sub

    Private Sub radioTodos_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles radioTodos.CheckedChanged
        If radioTodos.Checked = True Then
            Me.lstDepartamento.Enabled = False
        End If
    End Sub

    Private Sub radioDepto_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles radioDepto.CheckedChanged
        If radioDepto.Checked = True Then
            Me.lstDepartamento.Enabled = True
        Else
            Me.lstDepartamento.Enabled = False
        End If
    End Sub

    Private Sub RadButton2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        Me.Close()
    End Sub

    Private Sub btnAbrir_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)

    End Sub

    Private Sub ToolStripButton2_Click(sender As System.Object, e As System.EventArgs) Handles ToolStripButton2.Click
        If Me.lstMeses.Text = "" Or Me.lstMeses.Text = "(Seleccionar un mes)" Then
            MsgBox("Debes indicar un mes", MsgBoxStyle.Exclamation, "Aviso")
            Exit Sub
        End If

        If radioTodos.Checked = True Then
            todos()
        Else
            Depto()
        End If
    End Sub

    Private Sub ToolStripButton1_Click(sender As System.Object, e As System.EventArgs) Handles ToolStripButton1.Click
        Close()

    End Sub
End Class